package bandi.osgi.dal.dao.users.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import bandi.osgi.dal.dao.abstr.AbstractDAO;
import bandi.osgi.dal.dao.beans.User;
import bandi.osgi.dal.dao.users.UsersDAO;

public class UsersDAOImpl extends AbstractDAO implements UsersDAO {

	@Override
	public void deleteUser(User user) {
		final String sql = "DELETE FROM USERS WHERE USER_ID = "
				+ user.getUser_id();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = super.createConnection();
			statement = connection.createStatement();
			statement.executeUpdate(sql);
			statement.close();
		} catch (final Exception e) {
			System.err.println("Internal error: "+e.getMessage());
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (final SQLException ignored) {
					// Ignoring this exception, as we can't do much.
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (final SQLException ignored) {
					// Ignoring this exception, as we can't do much.
				}
			}
		}

	}

	@Override
	public List<User> getAllUsers() {
		final String sql = "SELECT * FROM USERS";
		Connection connection = null;
		Statement statement = null;
		final List<User> list = new ArrayList<User>();
		try {
			connection = super.createConnection();
			statement = connection.createStatement();
			final ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				list.add(new User(rs.getString("FIRST_NAME"), rs
						.getString("LAST_NAME"), rs.getString("PASSWORD"), rs
						.getLong("USER_ID"), rs.getString("USER_NAME")));
			}
			statement.close();
			return list;
		} catch (final Exception e) {
			System.err.println("Internal error: "+e.getMessage());
			return null;
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (final SQLException ignored) {
					// Ignoring this exception, as we can't do much.
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (final SQLException ignored) {
					// Ignoring this exception, as we can't do much.
				}
			}
		}
	}

	@Override
	public User getUser(long userID) {
		final String sql = "SELECT * FROM USERS WHERE USER_ID = " + userID;
		Connection connection = null;
		Statement statement = null;
		User u = null;
		try {
			connection = super.createConnection();
			statement = connection.createStatement();
			final ResultSet rs = statement.executeQuery(sql);
			rs.next();
			u = new User(rs.getString("FIRST_NAME"), rs.getString("LAST_NAME"),
					rs.getString("PASSWORD"), rs.getLong("USER_ID"), rs
							.getString("USER_NAME"));
			statement.close();
			return u;
		} catch (final Exception e) {
			System.err.println("Internal error :" + e.getMessage());
			return null;
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (final SQLException ignored) {
					// Ignoring this exception, as we can't do much.
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (final SQLException ignored) {
					// Ignoring this exception, as we can't do much.
				}
			}
		}
	}

	@Override
	public User getUser(String userName) {
		final String sql = "SELECT * FROM USERS WHERE USER_NAME = " + userName;
		Connection connection = null;
		Statement statement = null;
		User u = null;
		try {
			connection = super.createConnection();
			statement = connection.createStatement();
			final ResultSet rs = statement.executeQuery(sql);
			rs.next();
			u = new User(rs.getString("FIRST_NAME"), rs.getString("LAST_NAME"),
					rs.getString("PASSWORD"), rs.getLong("USER_ID"), rs
							.getString("USER_NAME"));
			statement.close();
			return u;
		} catch (final Exception e) {
			System.err.println("Internal error :" + e.getMessage());
			return null;
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (final SQLException ignored) {
					// Ignoring this exception, as we can't do much.
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (final SQLException ignored) {
					// Ignoring this exception, as we can't do much.
				}
			}
		}
	}

	@Override
	public void insertUser(User user) {
		final String sql = "INSERT INTO USERS(USER_ID, USER_NAME, FIRST_NAME, LAST_NAME, PASSWORD)"
				+ " VALUES("
				+ null
				+ ", '"
				+ user.getUser_name()
				+ "', '"
				+ user.getFirst_name()
				+ "', '"
				+ user.getLast_name()
				+ "', '" + user.getPassword() + "')";
		Connection connection = null;
		Statement statement = null;
		try {
			connection = super.createConnection();
			statement = connection.createStatement();
			statement.executeUpdate(sql);
			statement.close();
		} catch (final Throwable e) {
			System.err.println("Internal error: "+e.getMessage());
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (final SQLException ignored) {
					// Ignoring this exception, as we can't do much.
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (final SQLException ignored) {
					// Ignoring this exception, as we can't do much.
				}
			}
		}

	}

	@Override
	public void updateUser(User user) {
		final String sql = "UPDATE USERS SET USER_NAME = '"
				+ user.getUser_name() + "', FIRST_NAME = '" + user.getFirst_name() + "', LAST_NAME = '"
				+ user.getLast_name() + "', PASSWORD = '" + user.getPassword()
				+ "' WHERE USER_ID = " + user.getUser_id();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = super.createConnection();
			statement = connection.createStatement();
			statement.executeUpdate(sql);
			statement.close();
		} catch (final Throwable e) {
			System.err.println("Internal error: "+e.getMessage());
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (final SQLException ignored) {
					// Ignoring this exception, as we can't do much.
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (final SQLException ignored) {
					// Ignoring this exception, as we can't do much.
				}
			}
		}

	}

}
